import string, sys, traceback
from datetime import date, timedelta

from mokoconv.obj.category import Category
from mokoconv.obj.unit import Unit

try:
    from pysqlite2 import dbapi2 as sqlite3    
except:
    from sqlite3 import dbapi2 as sqlite3

class SqlUtil:

    def openDatabase(self, pathDatabase):
        ##!! check custom pathDatabase - exception - return true/false 
        print("try to load database " + pathDatabase)
        self.connection = sqlite3.connect(pathDatabase)
        return True
            
    def closeDatabase(self):
        
        if self.connection == None:
            return None
        
        self.connection.close()
        return True

    def getUnitRelation(self, id_unit_from, id_unit_to):
        
        if self.connection == None:
            return None
        
        cursor = self.connection.cursor()
        param = (id_unit_from, id_unit_to)
        cursor.execute("select relation from rel_unit_unit where id_unit_from = ? and id_unit_to = ?", param)
        
        # it should be only one relation :)
        return cursor.fetchone()[0]

    def loadCategories(self):
        
        if self.connection == None:
            return None
        
        cursor = self.connection.cursor()
        cursor.execute("select id_category, description from category")
        
        categories = []
        
        # return an array of card
        for (id_category, description) in cursor:
            category = Category(id_category, description);
            categories.append(category)
        
        cursor.close()
        return categories

    def loadUnits(self, id_category, no_id_unit):
        
        if self.connection == None:
            return None
        
        cursor = self.connection.cursor()
        
        if no_id_unit == None:
            param = (id_category,)
            cursor.execute("select id_unit, description, symbol from unit where id_category = ?", param)
        else:
            param = (id_category, no_id_unit)
            cursor.execute("select id_unit, description, symbol from unit where id_category = ? and id_unit != ?", param)

        units = []
        print "#########################"
        for (id_unit, description, symbol) in cursor:
            print description
            unit = Unit(id_unit, description, symbol)
            units.append(unit)
            
        cursor.close()
        return units
            
    def __init__(self):
        None